Group By Expression

Introduction

The previous examples illustrated how to group employees by department, where the database field dept_id was used as the field to group by.  It is also possible to group where an expression defines the grouping criteria. This will be demonstrated in this example where the employee list will be grouped by the first letter of the employee last name.  An expression will be developed to be used as the criteria for grouping.

The desired output is shown in the figure below:

This image shows the report to be created which groups employees by the first letter of the last name.

The steps to follow are:

  1. Sort by Employee Last Name.
  2. Create a Group Band and set its background color to light gray.
  3. Create an expression to obtain the first letter of the last name and place it within the “Group-by-field or expression” box.
  4. Place the expression into the Group Band Header to highlight the groupings (see the bold letters in the above figure).

Note: When grouping by a particular field (Employee last name in this example), the data must be sorted by the same field. 

Create the Basic Report

Begin by creating a report and within the “Edit Report” dialog box click the Sort Tab and sort by Employee Last Name.

Within the Edit Report dialog box, click the Design button to bring up the Band Report Editor and create a simple report as shown in the figure below:

This image shows the report design prior to creating groups. It contains the last name, first name, hire date, salary, city, and region fields.

Create a Band Group

Create a band group by clicking the “Create a Band Group” icon on the Band Report Editor toolbar. The Header dialog box will appear as shown below. Since grouping by the first letter of the employee last name is to be done, you need to enter an appropriate expression into the “Group-by-field or expression” box.

Add a color to the band by clicking the “Color” button, then select light gray.

Dialog where the Group Header band is created. Various properties are entered in this dialog such as font, color, link with band, printing options, related master and footer band names.

Create the Expression

To create the expression, click the ellipsis button to the right of the “Group-by-field or expression” box to launch the Expression Wizard. Click Function, then select the COPY function.

The Expression Wizard dialog where the three parameters of the copy command are entered. Argument 1 is the text value, and arguments 2 and 3 are numeric values.

The COPY function returns a substring, and for this example you need to choose the Employee Last Name field from the database (using the same method as in previous examples) and return the first character from the string by entering the following:

The Expression Wizard dialog with parameters filled-in for the copy command. The first parameters contains the last name, with the second and third parameters containing a value of one.

The expression to return the first letter of the last name has now been inserted into the Group-by-field or expression box. The grouping is now handled by an expression, as opposed to an earlier example where grouping was done by a database field name.

The band options dialog with the copy function entered into the "Group by field or expressions" control.

Place Expression into Group Header Band

The final step is to place the same expression into the Group Header such that the first letter of the last name is displayed within the Group Header. To accomplish this, click E=mc2 on the toolbar, then click within the Group Header Band. Then enter the same expression as before.

The copy function as entered into the Edit Expression Field dialog box.

Drag the field containing the expression to the left edge, then set the font to 14 point bold.

The desired report will print as follows:

This image shows the completed report with employee names grouped by the first letter of the last name.